package com.guigu.qingcheng.dao;

import com.qingcheng.pojo.goods.Category;
import com.qingcheng.pojo.goods.Spu;
import com.qingcheng.pojo.goods.TbCategory;
import com.qingcheng.pojo.goods.TbSpu;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.RequestParam;

import java.time.LocalDate;
import java.util.List;

@Component
public interface LocalMapper {

    /*查询家用电器下不同电器的售卖情况*/
    @Select("SELECT cate.* FROM tb_category cate INNER JOIN qingcheng_order.tb_order_item oi \n" +
            "INNER JOIN qingcheng_order.tb_order o \n" +
            "ON o.id=oi.order_id\n" +
            "WHERE o.pay_status='1' \n" +
            "AND cate.parent_id= #{parent_id}\n" +
            "AND o.create_time>= #{date1} \n" +
            "AND o.create_time<= #{date2}\n" +
            "AND oi.category_id2=cate.id\n" +
            "GROUP BY cate.name\n" +
            "LIMIT 6")
    public List<TbCategory> appliances(@RequestParam("parent_id")String parent_id,
                                       @RequestParam("date1") LocalDate date1,
                                       @RequestParam("date2") LocalDate date2);

    /*如果一个都没有卖出则使用这个*/
    @Select("    SELECT * FROM `tb_category`\n" +
            "    WHERE parent_id=#{parent_id}\n" +
            "    LIMIT 6\n")
    public List<TbCategory> Nullappliances(@RequestParam("parent_id")String parent_id);


    /*
     *循环查询个个热门分类的的商品
     * */
    @Select("SELECT DISTINCT spu.* FROM tb_category cate \n" +
            "INNER JOIN tb_spu spu\n" +
            "INNER JOIN qingcheng_order.tb_order o \n" +
            "INNER JOIN qingcheng_order.tb_order_item oi\n" +
            "ON oi.category_id3=cate.id\n" +
            "WHERE spu.category3_id=cate.id\n" +
            "AND o.create_time>= #{date1} \n" +
            "AND o.create_time<= #{date2} \n" +
            "AND o.pay_status='1'\n" +
            "AND cate.id= #{categoryid}\n" +
            "ORDER BY RAND() LIMIT 7")
    List<TbSpu> HotGoods(@RequestParam("categoryid") Integer categoryid,
                         @RequestParam("date1") LocalDate date1,
                         @RequestParam("date2") LocalDate date2);

    /*如果一个都没有卖出则使用这个*/
    @Select("SELECT DISTINCT spu.* FROM tb_category cate INNER JOIN tb_spu spu\n" +
            "WHERE spu.category3_id=cate.id\n" +
            "AND cate.id= #{categoryid}\n" +
            "ORDER BY RAND() LIMIT 7")
    public List<TbSpu> NullHotGoods(@RequestParam("categoryid") Integer categoryid);


}
